﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace BT_web__nhom06
{
    public partial class QL_ThongTinDV : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //string sqlmachidoan = "select machidoan,tenchidoan from chidoan";
            //DataTable tblchidoan = Tienichsql.ketnoicsdl.ChayLenhSelectDuLieu("select machidoan,tenchidoan from chidoan");
            //drlLocTheoChiDoan.DataSource = tblchidoan;
            //drlLocTheoChiDoan.DataTextField = "tenchidoan";
            //drlLocTheoChiDoan.DataValueField = "machidoan";
            //drlLocTheoChiDoan.DataBind();

            if (IsPostBack == false)
            {
                
                DataTable tbl = Tienichsql.ketnoicsdl.ChayLenhSelectDuLieu("SELECT masinhvien ,ho ,ten ,ngaysinh ,dienthoailienhe  FROM doanvien ");
                gvDoanVien.DataSource = tbl;
                gvDoanVien.DataBind();
            }
        }

        protected void btnApDungLoc_Click(object sender, EventArgs e)
        {
            string dieukienloc = " where ";

            //if (drlLocTheoChiDoan.Text != null)
            //    dieukienloc = dieukienloc + " machidoan='" + drlLocTheoChiDoan.Text + "'";

            if (string.IsNullOrEmpty(txtLocTheoTenDoanVien.Text) == false)
                dieukienloc = dieukienloc + " ten like N'%" + txtLocTheoTenDoanVien.Text + "%'";  //so sánh chuỗi unicode

            if (string.IsNullOrEmpty(txtLocTheoSoDienThoai.Text) == false)
                dieukienloc = dieukienloc + " DienThoaiLienHe like N'%" + txtLocTheoSoDienThoai.Text + "%'";

            if (dieukienloc == " where ") //chưa nhập giá trị lọc
                dieukienloc = "";


            string lenhSelectSQL = "SELECT masinhvien ,ho ,ten ,ngaysinh ,dienthoailienhe  FROM doanvien " + dieukienloc;
            //lenhSelectSQL+=dieukienloc;
            DataTable tbl2 = Tienichsql.ketnoicsdl.ChayLenhSelectDuLieu(lenhSelectSQL);

            gvDoanVien.DataSource = tbl2;
            gvDoanVien.DataBind();

            //gvDoanVien


        }

        protected void drlLocTheoChiDoan_SelectedIndexChanged(object sender, EventArgs e)
        {
            string lenhSelectSQL = "SELECT masinhvien ,ho ,ten ,ngaysinh ,dienthoailienhe  FROM doanvien where machidoan like N'%" + drlLocTheoChiDoan.Text + "%'";
            DataTable tbl2 = Tienichsql.ketnoicsdl.ChayLenhSelectDuLieu(lenhSelectSQL);
            gvDoanVien.DataSource = tbl2;
            gvDoanVien.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string pSQL = "Select masinhvien, ho, ten, hinhanh, ngaysinh, gioitinh, ngayvaodoan, diachithuongtru, diachinoiohientai, dienthoailienhe, email, chidoan.tenchidoan, noichuyenden, ngaychuyenden, noichuyendi, ngaychuyendi, tinhtrang from doanvien, chidoan where doanvien.machidoan = chidoan.machidoan";
            DataTable pBang;
            pBang = Tienichsql.ketnoicsdl.ChayLenhSelectDuLieu(pSQL);

            string name_ = "DSDoanVien"; //Tên file excel mà bạn lưu về máy

            
            //Tạo mới bảng để chép vào file excel
            Table tb = new Table();
            //Định dạng bảng
            tb.BorderColor = System.Drawing.Color.FromName("red");
            tb.CellPadding = 4;
            tb.GridLines = GridLines.Both;
            tb.CellSpacing = 0;
            tb.Width = Unit.Percentage(100);
            TableCell cell;
            TableRow row;
            int from = 0;
            int to = pBang.Rows.Count;
            int header = 0;
            for (int i = from; i < to ; i++)
            {
                DataRow dr = pBang.Rows[i];
                if (header == 0)
                {
                    row = new TableRow();
                    cell = new TableCell();
                    //Ma sinh vien
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Mã Sinh Viên</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Họ
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.Width = 150;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Text = "<b>Họ</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Tên
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Tên</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Hình ảnh
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Hình ảnh</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Ngày sinh
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Ngày sinh</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Giới tính
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Giới tính</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Ngày vào đoàn
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Ngày vào đoàn</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Địa chỉ thường trú
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Địa chỉ thường trú</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Địa chỉ nơi ở hiện tại
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Địa chỉ nơi ở hiện tại</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //điện thoại liên hệ
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Điện thoại liên hệ</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Email
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Email</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //mã chi đoàn
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Mã chi đoàn</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //Nơi chuyển đến
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Nơi chuyển đến</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //ngay chuyen den
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Ngày chuyển đến</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //noi chuyen di
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Nơi chuyển đi</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //ngay chuyen di
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 150;
                    cell.Text = "<b>Ngày chuyển đi</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    //tình trang
                    cell = new TableCell();
                    cell.Height = 50;
                    cell.BackColor = System.Drawing.Color.FromName("red");
                    cell.Width = 0;
                    cell.Text = "<b>Tình trạng</b>";
                    cell.HorizontalAlign = HorizontalAlign.Center;
                    row.Cells.Add(cell);
                    tb.Rows.Add(row);

                }

                header++;
                row = new TableRow();
                cell = new TableCell();

                //mã sinh viên
                cell.Height = 50;
                cell.Text = dr[0].ToString();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);

                //Ho
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[1].ToString();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);

                //Ten

                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[2].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //hinh anh
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[3].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //ngay sinh
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[4].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //gioi tinh
                cell = new TableCell();
                cell.Height = 50;
                if((bool)dr[5] == true)
                { cell.Text = "Nam"; }
                else { cell.Text = "Nữ"; }
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Ngay vao doan
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[6].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //dia chi thuong tru
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[7].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //dia chi noi o hien tai
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[8].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Dien thoai lien he
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[9].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Email
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[10].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Ma chi doan
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[11].ToString();
                
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //noi chuyen den
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[12].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //ngay chuyen den
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[13].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Noi chuyen di
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[14].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //ngay chuyen di
                cell = new TableCell();
                cell.Height = 50;
                cell.Text = dr[15].ToString().Trim();
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                //Tinh trang
                cell = new TableCell();
                cell.Height = 50;
                if((string)dr[16]=="1")
                { cell.Text = "Đang hoạt động"; }
                else
                { cell.Text = "Đã chuyển đi"; }
 
                cell.HorizontalAlign = HorizontalAlign.Left;
                row.Cells.Add(cell);
                tb.Rows.Add(row);

            }

            //- Tương tự bạn có bao nhiêu trường cần export thì bạn chèn bấy nhiêu cột.

            //

            Response.Clear();

            Response.Buffer = true;

            //excel

            string ex_ = "xls";

            Context.Response.AddHeader("Content-Disposition", "attachment; filename=" + name_ + "." + ex_);

            //    Context.Response.AddHeader("Content-Length", strpath.Length.ToString());

            Response.ContentType = "application/vnd.ms-excel";

            Response.Charset = "UTF-8";

            this.EnableViewState = false;

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

            tb.RenderControl(oHtmlTextWriter);

            Response.Write(oStringWriter.ToString());
            Response.End();
        }

        protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {

        }


    }
}